MySQL

What is Mysql 5.1?

MySQL is a database system used on the web. Basically, a MySQL database allows you to create a relational database structure on a web-server somewhere in order to store data or automate procedures. If you think of it in comparison to Microsoft Access, MySQL is what holds all of your tables, PHP acts as your queries (among other things), and your forms are basically web pages with fields in them. With all of this combined, you can create truly spectacular projects on the web.

Importing .sql files into new MySQL database

SSH into new server
put SQL file on new server
create empty DB on new server

run from command line:

mysql -uUSERNAME -pPASSWORD DATABASENAME < FILE.SQL

What are the different tables present in MySQL?

Total 5 types of tables we can create
1. MyISAM
2. Heap
3. Merge
4. INNO DB
5. ISAM
MyISAM is the default storage engine as of MySQL 3.23. When you fire the above create query MySQL will create a MyISAM table.

What is INDEX in Mysql

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

Advantages of MySQL Indexes

Generally speaking, MySQL indexing into database gives you three advantages:

* Query optimization: Indexes make search queries much faster.
* Uniqueness: Indexes like primary key index and unique index help to avoid duplicate row data.
* Text searching: Full-text indexes in MySQL version 3.23.23, users have the opportunity to optimize searching against even large amounts of text located in any field indexed as such.

Disadvantages of MySQL indexes

When an index is created on the column(s), MySQL also creates a separate file that is sorted, and contains only the field(s) you're interested in sorting on.

Firstly, the indexes take up disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size.

Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.

How to create an index ? explain ?

An index is a performance-tuning method of allowing faster retrieval of records.
An index creates an entry for each value that appears in the indexed columns.
By default, Oracle creates B-tree indexes.

Syntax:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];

Example:
CREATE INDEX supplier_idx
ON supplier (supplier_name);

Joins in Mysql

Normal join query:

SELECT food.Meal, family.Position
FROM family, food
WHERE food.Position = family.Position

left join

SELECT family.Position, food.Meal
FROM family LEFT JOIN food
ON family.Position = food.Position

Mysql Predefined Functions

* MySQL Group By Clause - The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
* MySQL IN Clause - This is a clause which can be used alongwith any MySQL query to specify a condition.
* MySQL BETWEEN Clause - This is a clause which can be used alongwith any MySQL query to specify a condition.
* MySQL UNION Keyword - Use a UNION operation to combine multiple result sets into one.
* MySQL COUNT Function - The MySQL COUNT aggregate function is used to count the number of rows in a database table.
* MySQL MAX Function - The MySQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
* MySQL MIN Function - The MySQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
* MySQL AVG Function - The MySQL AVG aggregate function selects the average value for certain table column.
* MySQL SUM Function - The MySQL SUM aggregate function allows selecting the total for a numeric column.
* MySQL SQRT Functions - This is used to generate a square root of a given number.
* MySQL RAND Function - This is used to generate a random number using MySQL command.
* MySQL CONCAT Function - This is used to concatenate any string inside any MySQL command.
* MySQL DATE and Time Functions - Complete list of MySQL Date and Time related functions.
* MySQL Numeric Functions - Complete list of MySQL functions required to manipulate numbers in MySQL.
* MySQL String Functions - Complete list of MySQL functions required to manipulate strings in MySQL.

InnoDB vs MYISAM

myisam is better for high read volumes, innodb for high update volumes due to table vs row locking.

innodb is journaled, and can recover from crashes where myisam can't, much like NTFS vs FAT file systems.

myisam has full-text indexing, innodb doesn't.

innodb has transaction support, commits and rollbacks, myisam lacks these.